﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FirebirdSql.Data.FirebirdClient;
using System.IO;
using System.Security.Cryptography;
using System.Drawing;
using System.Drawing.Imaging;

namespace file_client
{
    /// <summary>
    /// Основной класс дел досье
    /// </summary>
    class DBActive
    {
        public FbConnection Connection;
        public FbTransaction Transaction;
        private string StartFolder;
        public int id { get; private set; }
        public DBActive(string ConnectionString, string StartFolder )
        {
            Connection = new FbConnection(ConnectionString);
            Connection.Open();
            if (Connection.State == System.Data.ConnectionState.Broken || Connection.State == System.Data.ConnectionState.Closed)
                throw new Exception("Подключение не удачное");
            this.StartFolder = StartFolder;
            this.id = 0;
            this.isLoaded = false;
            Transaction = Connection.BeginTransaction();
            FbCommand cmd = Connection.CreateCommand();
            cmd.Transaction = Transaction;
            cmd.CommandText = "INSERT INTO FILES (NOTE) VALUES (NULL) RETURNING ID";
            this.id = int.Parse(cmd.ExecuteScalar().ToString());
        }
        public DBActive(string ConnectionString, string StartFolder, int id)           
        {
            this.id = id;
            Connection = new FbConnection(ConnectionString);
            Connection.Open();
            if (Connection.State == System.Data.ConnectionState.Broken || Connection.State == System.Data.ConnectionState.Closed)
                throw new Exception("Подключение не удачное");
            this.StartFolder = StartFolder;
            this.isLoaded = false;
            Transaction = Connection.BeginTransaction();
            FbCommand cmd = Connection.CreateCommand();
            cmd.Transaction = Transaction;
            if (id == 0)
            {
                cmd.CommandText = "INSERT INTO FILES (NOTE) VALUES (NULL) RETURNING ID";
                this.id = int.Parse(cmd.ExecuteScalar().ToString());
            }
            cmd.CommandText = "SELECT * FROM FILES WHERE ID=" + this.id.ToString();
            FbDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                if (!dr.IsDBNull(dr.GetOrdinal("MAINFOTO"))) this._mainfoto = dr.GetInt32(dr.GetOrdinal("MAINFOTO"));
                else this._mainfoto = 0;
                if (!dr.IsDBNull(dr.GetOrdinal("SURNAME"))) this._surname = dr.GetString(dr.GetOrdinal("SURNAME"));
                else this._surname = "";
                if (!dr.IsDBNull(dr.GetOrdinal("NAME"))) this._name = dr.GetString(dr.GetOrdinal("NAME"));
                else this._name = "";
                if (!dr.IsDBNull(dr.GetOrdinal("NICK"))) this._nick = dr.GetString(dr.GetOrdinal("NICK"));
                else this._nick = "";
                if (!dr.IsDBNull(dr.GetOrdinal("ADDRESS"))) this._address = dr.GetString(dr.GetOrdinal("ADDRESS"));
                else this._address = "";
                if (!dr.IsDBNull(dr.GetOrdinal("RAJON_OFFICIAL"))) this._rajonofficial = dr.GetInt32(dr.GetOrdinal("RAJON_OFFICIAL"));
                else this._rajonofficial = 0;
                if (!dr.IsDBNull(dr.GetOrdinal("RAJON_NONOFFICIAL"))) this._rajonnonofficial = dr.GetInt32(dr.GetOrdinal("RAJON_NONOFFICIAL"));
                else this._rajonnonofficial = 0;
                if (!dr.IsDBNull(dr.GetOrdinal("NOTE"))) this._note = dr.GetString(dr.GetOrdinal("NOTE"));
                else this._note = "";
                if (!dr.IsDBNull(dr.GetOrdinal("SOURCE"))) this._source = dr.GetString(dr.GetOrdinal("SOURCE"));
                else this._source = "";
                if (!dr.IsDBNull(dr.GetOrdinal("DETECTION"))) this._detection = dr.GetString(dr.GetOrdinal("DETECTION"));
                else this._detection = "";
                if (!dr.IsDBNull(dr.GetOrdinal("ADDING"))) this._adding = dr.GetDateTime(dr.GetOrdinal("ADDING"));
                else this._adding = DateTime.Now;
                this.isLoaded = true;
            }
            dr.Close();
            cmd.Dispose();
        }
        ~DBActive()
        {
            if (Connection.State != System.Data.ConnectionState.Broken && Connection.State != System.Data.ConnectionState.Closed)
            {
                if (Transaction!=null && Transaction.Connection!=null) Transaction.Rollback();
                Connection.Close();
            }
                
        }

        public void Delete()
        {
            FbCommand cmd = Connection.CreateCommand();
            cmd.Transaction = Transaction;
            cmd.CommandText = "DELETE FROM FILES WHERE ID=" + id.ToString();
            cmd.ExecuteNonQuery();
            this.isLoaded = false;
            Transaction.Commit();
            Transaction = Connection.BeginTransaction();

        }

        public void Save() {
            FbCommand cmd = Connection.CreateCommand();
            cmd.Transaction = Transaction;
            cmd.CommandText = "UPDATE OR INSERT INTO FILES (ID, MAINFOTO, SURNAME, NAME, NICK, ADDRESS, RAJON_OFFICIAL, RAJON_NONOFFICIAL, NOTE, SOURCE, DETECTION) "
                + "VALUES (" + ((id==0)?"NULL":id.ToString()) + ", @MAINFOTO, @SURNAME, @NAME, @NICK, @ADDRESS, @RAJON_OFFICIAL, @RAJON_NONOFFICIAL, @NOTE, @SOURCE, @DETECTION) "
                            + "RETURNING ID, ADDING ";//INTO @ID, @ADDING
            if (this._mainfoto == 0) cmd.Parameters.Add("MAINFOTO", FbDbType.Integer).IsNullable = true;
            else cmd.Parameters.Add("MAINFOTO", FbDbType.Integer).Value = _mainfoto;
            if (this._surname == "") cmd.Parameters.Add("SURNAME", FbDbType.VarChar, 100).IsNullable = true;
            else cmd.Parameters.Add("SURNAME", FbDbType.VarChar, 100).Value = _surname;
            if (this._name == "") cmd.Parameters.Add("NAME", FbDbType.VarChar, 100).IsNullable = true;
            else cmd.Parameters.Add("NAME", FbDbType.VarChar, 100).Value = _name;
            if (this._nick == "") cmd.Parameters.Add("NICK", FbDbType.VarChar, 100).IsNullable = true;
            else cmd.Parameters.Add("NICK", FbDbType.VarChar, 100).Value = _nick;
            if (this._address == "") cmd.Parameters.Add("ADDRESS", FbDbType.VarChar, 1000).IsNullable = true;
            else cmd.Parameters.Add("ADDRESS", FbDbType.VarChar, 1000).Value = _address;
            if (this._rajonofficial == 0) cmd.Parameters.Add("RAJON_OFFICIAL", FbDbType.Integer).IsNullable = true;
            else cmd.Parameters.Add("RAJON_OFFICIAL", FbDbType.Integer).Value = _rajonofficial;
            if (this._rajonnonofficial == 0) cmd.Parameters.Add("RAJON_NONOFFICIAL", FbDbType.Integer).IsNullable = true;
            else cmd.Parameters.Add("RAJON_NONOFFICIAL", FbDbType.Integer).Value = _rajonnonofficial;
            if (this._note == "") cmd.Parameters.Add("NOTE", FbDbType.VarChar, 10000).IsNullable = true;
            else cmd.Parameters.Add("NOTE", FbDbType.VarChar, 10000).Value = _note;
            if (this._source == "") cmd.Parameters.Add("SOURCE", FbDbType.VarChar, 1000).IsNullable = true;
            else cmd.Parameters.Add("SOURCE", FbDbType.VarChar, 1000).Value = _source;
            if (this._detection == "") cmd.Parameters.Add("DETECTION", FbDbType.VarChar, 1000).IsNullable = true;
            else cmd.Parameters.Add("DETECTION", FbDbType.VarChar, 1000).Value = _detection;

            FbParameter paramId = new FbParameter("ID", FbDbType.Integer);
            paramId.Direction = System.Data.ParameterDirection.ReturnValue;
            paramId.IsNullable = true;
            cmd.Parameters.Add(paramId);
            FbParameter paramAdding = new FbParameter("ADDING", FbDbType.TimeStamp);
            paramAdding.Direction = System.Data.ParameterDirection.ReturnValue;
            paramAdding.IsNullable = true;
            cmd.Parameters.Add(paramAdding);
            cmd.ExecuteScalar(); // с ExecuteNonQuery не работает
            this.id = int.Parse(paramId.Value.ToString());
            this._adding = DateTime.Parse(paramAdding.Value.ToString());
            cmd.Dispose();

            //this.MainFoto.Save();

            Transaction.Commit();
            Transaction = Connection.BeginTransaction();

        }

        #region Свойства таблицы "FILES"
        public bool isLoaded { get; private set; }

        private int _mainfoto;
        public Storage MainFoto
        {
            get { return new Storage(this, _mainfoto); }
            set { _mainfoto = value.id; }
        }

        private string _surname;

        public string Surname
        {
            get { return _surname; }
            set { _surname = value; }
        }

        private string _name;

        public string Name
        {
            get { return _name; }
            set { _name = value; }
        }

        private string _nick;

        public string Nick
        {
            get { return _nick; }
            set { _nick = value; }
        }

        private string _address;

        public string Address
        {
            get { return _address; }
            set { _address = value; }
        }

        private int _rajonofficial;

        public RajonOfficial RajonOfficialValue
        {
            get 
            { 
                if (_rajonofficial==0) return null;
                return new RajonOfficial(this, _rajonofficial);
            }
            set 
            {
                if (value != null) _rajonofficial = value.id;
                else _rajonofficial = 0;
            }
        }

        private int _rajonnonofficial;

        public RajonNonOfficial RajonNonOfficialValue
        {
            get
            {
                if (_rajonnonofficial == 0) return null;
                return new RajonNonOfficial(this, _rajonnonofficial);
            }
            set
            {
                if (value != null) _rajonnonofficial = value.id;
                else _rajonnonofficial = 0;
            }
        }

        private string _note;

        public string Note
        {
            get { return _note; }
            set { _note = value; }
        }

        private string _source;

        public string Source
        {
            get { return _source; }
            set { _source = value; }
        }

        private string _detection;

        public string Detection
        {
            get { return _detection; }
            set { _detection = value; }
        }

        private DateTime _adding;

        public DateTime Adding
        {
            get { return _adding; }
        }

        public Storage[] Storages
        {
            get { return Storage.LoadAll(this).ToArray(); }
        }

        public List<Phone> Phones
        {
            get
            {      
                return Phone.LoadAll(this);
            }
        }

        public List<Party> Partys
        {
            get
            {
                return Party.LoadAll(this);
            }
        }

        public int[] Links
        {
            get
            {
                List<int> result = new List<int>();
                FbCommand cmd = Connection.CreateCommand();
                cmd.Transaction = Transaction;
                cmd.CommandText = "SELECT ANCHOR FROM LINKFILE WHERE REFERENCE=" + id.ToString();
                FbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    int __id = dr.GetInt32(dr.GetOrdinal("ANCHOR"));
                    if (!result.Contains(__id)) result.Add(__id);
                }
                dr.Close();
                cmd.CommandText = "SELECT REFERENCE FROM LINKFILE WHERE ANCHOR=" + id.ToString();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    int __id = dr.GetInt32(dr.GetOrdinal("REFERENCE"));
                    if (!result.Contains(__id)) result.Add(__id);
                }
                dr.Close();
                cmd.Dispose();
                return result.ToArray();
            }
        }

        public List<Action> Actions
        {
            get
            {
                return Action.LoadAll(this);
            }
        }

        #endregion Свойства таблицы "FILES"

        public void AddLink(int IDLink)
        {
            FbCommand cmd = Connection.CreateCommand();
            cmd.Transaction = Transaction;
            cmd.CommandText = "INSERT INTO LINKFILE (REFERENCE, ANCHOR) VALUES (" + id.ToString() + "," + IDLink.ToString() + ")";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO LINKFILE (ANCHOR, REFERENCE) VALUES (" + id.ToString() + "," + IDLink.ToString() + ")";
            cmd.ExecuteNonQuery();
        }

        public void DeleteLink(int IDLink)
        {
            FbCommand cmd = Connection.CreateCommand();
            cmd.Transaction = Transaction;
            cmd.CommandText = "DELETE FROM LINKFILE WHERE (REFERENCE=" + IDLink.ToString() + ")";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "DELETE FROM LINKFILE WHERE (ANCHOR=" + IDLink.ToString() + ")";
            cmd.ExecuteNonQuery();
        }

        public class Storage
        {
            public Image Thumbnail
            {
                get
                {
                    if (isPicture)
                    {
                        Bitmap _result = new Bitmap(Path.Combine(Parent.StartFolder, this.Reference));
                        return  _result.GetThumbnailImage(48,48, null , IntPtr.Zero);
                    }
                    return null;
                }
            }

            public override int GetHashCode()
            {
                return base.GetHashCode();
            }
            /// <summary>
            /// Загружает файл из базы
            /// </summary>
            /// <param name="parent"></param>
            /// <param name="id"></param>
            public Storage(DBActive parent, int id)
            {
                this._parent = parent;
                this._id = id;
                this._note = "";
                this._filename = "";
                this._reference = "";
                FbCommand cmd = _parent.Connection.CreateCommand();
                cmd.Transaction = _parent.Transaction;
                cmd.CommandText = "SELECT NOTE, FILENAME, REFERENCE FROM LINKSTORAGE WHERE ID=" + id.ToString();
                FbDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    if (!dr.IsDBNull(dr.GetOrdinal("NOTE"))) _note = dr.GetString(dr.GetOrdinal("NOTE"));
                    if (!dr.IsDBNull(dr.GetOrdinal("FILENAME"))) _filename = dr.GetString(dr.GetOrdinal("FILENAME"));
                    if (!dr.IsDBNull(dr.GetOrdinal("REFERENCE"))) _reference = dr.GetString(dr.GetOrdinal("REFERENCE"));
                }
                dr.Close();
                cmd.Dispose();
            }

            /// <summary>
            /// Сохраняет файл в базу
            /// </summary>
            /// <param name="parent"></param>
            /// <param name="filename"></param>
            /// <param name="note"></param>
            public Storage(DBActive parent, string filename, string note)
            {
                this._parent = parent;
                if (this._parent.id == 0) throw new Exception("До создания дела добавлять файлы нельзя");
                MD5 md5 = new MD5CryptoServiceProvider();
                FileInfo fi = new FileInfo(filename);
                byte[] fileData = File.ReadAllBytes(filename);
                byte[] checkSum = md5.ComputeHash(fileData);
                string hash = BitConverter.ToString(checkSum).Replace("-", String.Empty);
                string newfile = hash.Substring(2) + fi.Extension.ToUpper();
                string newfolder = hash.Substring(0, 2);
                Directory.CreateDirectory(Path.Combine(this._parent.StartFolder, newfolder));
                if (!File.Exists(Path.Combine(this._parent.StartFolder, newfolder, newfile)))
                    File.Copy(filename, Path.Combine(this._parent.StartFolder, newfolder, newfile), true);
                try
                {
                    FbCommand cmd = this._parent.Connection.CreateCommand();
                    cmd.Transaction = _parent.Transaction;
                    cmd.CommandText = "INSERT INTO LINKSTORAGE (\"FILE\", NOTE, FILENAME, REFERENCE) VALUES (@FILE,@NOTE,@FILENAME,@REFERENCE) RETURNING ID";
                    cmd.Parameters.Add("FILE", FbDbType.Integer).Value = this._parent.id;
                    cmd.Parameters.Add("NOTE", FbDbType.VarChar, 1000).Value = note;
                    cmd.Parameters.Add("FILENAME", FbDbType.VarChar, 255).Value = fi.Name;
                    cmd.Parameters.Add("REFERENCE", FbDbType.VarChar, 255).Value = newfolder + "\\" + newfile;
                    this._id = int.Parse(cmd.ExecuteScalar().ToString());
                    cmd.Dispose();
                }
                catch { }
                this._note = note;
                this._reference = newfolder + "\\" + newfile;
                this._filename = fi.Name;
            }

            public Storage(DBActive parent)
            {
                this._id = 0;
                this._parent = parent;
                this._filename = "";
                this._reference = "";
                this._note = "";
            }

            public static List<Storage> LoadAll(DBActive parent)
            {
                List<Storage> result = new List<Storage>();
                FbCommand cmd = parent.Connection.CreateCommand();
                cmd.Transaction = parent.Transaction;
                cmd.CommandText = "SELECT ID, NOTE, FILENAME, REFERENCE FROM LINKSTORAGE WHERE \"FILE\"=" + parent.id.ToString();
                FbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Storage _storage = new Storage(parent);
                    _storage._id = dr.GetInt32(dr.GetOrdinal("ID"));
                    if (!dr.IsDBNull(dr.GetOrdinal("NOTE"))) _storage._note = dr.GetString(dr.GetOrdinal("NOTE"));
                    if (!dr.IsDBNull(dr.GetOrdinal("FILENAME"))) _storage._filename = dr.GetString(dr.GetOrdinal("FILENAME"));
                    if (!dr.IsDBNull(dr.GetOrdinal("REFERENCE"))) _storage._reference = dr.GetString(dr.GetOrdinal("REFERENCE"));
                    result.Add(_storage);
                }
                dr.Close();
                cmd.Dispose();
                return result;
            }
            /// <summary>
            /// Удаляет файл из базы
            /// </summary>
            public void Delete()
            {
                FbCommand cmd = _parent.Connection.CreateCommand();
                cmd.Transaction = _parent.Transaction;
                cmd.CommandText = "DELETE FROM LINKSTORAGE WHERE ID=" + _id.ToString();
                cmd.ExecuteNonQuery();
                // проверяем ссылки в базе на этот файл
                cmd.CommandText = "SELECT COUNT(*) FROM LINKSTORAGE WHERE REFERENCE=@REFERENCE";
                cmd.Parameters.Add("REFERENCE", FbDbType.VarChar, 255).Value = _reference;
                int cnt = int.Parse(cmd.ExecuteScalar().ToString());
                // Выставить в деле пустую картинку в качестве главной
                cmd.CommandText = "UPDATE FILES SET MAINFOTO=NULL WHERE ID = " + Parent.id.ToString();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                // Удаляем файл только если на него нет больше ссылок
                try
                {
                    if (cnt == 0) File.Delete(Path.Combine(_parent.StartFolder, _reference));
                }
                catch (IOException) { }
                this._id = 0;
                this._note = "";
                this._filename = "";
                this._reference = "";
            }

            public bool isPicture
            {
                get
                {
                    if (this._filename.ToUpper().EndsWith(".PNG")) return true;
                    if (this._filename.ToUpper().EndsWith(".JPG")) return true;
                    if (this._filename.ToUpper().EndsWith(".JPEG")) return true;
                    if (this._filename.ToUpper().EndsWith(".BMP")) return true;
                    if (this._filename.ToUpper().EndsWith(".GIF")) return true;
                    return false;
                }
            }

            private int _id;
            public int id
            {
                get { return _id; }
            }

            private DBActive _parent;

            public DBActive Parent
            {
                get { return _parent; }
            }

            private string _filename;

            public string Filename
            {
                get { return _filename; }
                set { _filename = value; }
            }

            private string _note;

            public string Note
            {
                get { return _note; }
                set { _note = value; }
            }

            private string _reference;

            public string Reference
            {
                get { return _reference; }
            }

            public void Save()
            {
                if (this._id != 0)
                {
                    FbCommand cmd = this._parent.Connection.CreateCommand();
                    cmd.Transaction = _parent.Transaction;
                    cmd.CommandText = "UPDATE LINKSTORAGE SET NOTE=@NOTE, FILENAME=@FILENAME WHERE ID=" + this._id.ToString();
                    cmd.Parameters.Add("NOTE", FbDbType.VarChar, 1000).Value = this._note;
                    cmd.Parameters.Add("FILENAME", FbDbType.VarChar, 255).Value = this._filename;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }

            public override string ToString()
            {
                return this._filename;
            }

            public override bool Equals(object obj)
            {
                if (obj is Storage) return (this.id == (obj as Storage).id);
                else return false;
            }
        }

        public class Phone
        {
            public Phone(DBActive parent, int id)
                : this(parent)
            {
                this.id = id;
                FbCommand cmd = parent.Connection.CreateCommand();
                cmd.Transaction = parent.Transaction;
                cmd.CommandText = "SELECT PHONE, CATEGORY FROM PHONES WHERE ID=" + id.ToString();
                FbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    if (!dr.IsDBNull(dr.GetOrdinal("PHONE"))) this.PhoneNumber = dr.GetString(dr.GetOrdinal("PHONE"));
                    else this.PhoneNumber = "";
                    if (!dr.IsDBNull(dr.GetOrdinal("CATEGORY"))) this.Category = dr.GetInt32(dr.GetOrdinal("CATEGORY"));
                    else this.Category = -1;
                }
                dr.Close();
                cmd.Dispose();

            }
            public Phone(DBActive parent)
            {
                this.Parent = parent;
                this.id = 0;
                this.PhoneNumber = "";
                this.Category = -1;
            }

            public Phone(DBActive parent, string phone, int category):this(parent)
            {
                this.PhoneNumber = phone;
                this.Category = category;
                FbCommand cmd = parent.Connection.CreateCommand();
                cmd.Transaction = parent.Transaction;
                cmd.CommandText = "INSERT INTO PHONES (\"FILE\", PHONE, CATEGORY) VALUES (@FILE,@PHONE,@CATEGORY) RETURNING ID";
                cmd.Parameters.Add("FILE", FbDbType.Integer).Value = this.Parent.id;
                cmd.Parameters.Add("PHONE", FbDbType.VarChar, 16).Value = phone;
                cmd.Parameters.Add("CATEGORY", FbDbType.Integer).Value = category;
                this.id = int.Parse(cmd.ExecuteScalar().ToString());
                cmd.Dispose();
            }
            public static List<Phone> LoadAll(DBActive parent) 
            {
                List<Phone> result = new List<Phone>();
                FbCommand cmd = parent.Connection.CreateCommand();
                cmd.Transaction = parent.Transaction;
                cmd.CommandText = "SELECT ID, PHONE, CATEGORY FROM PHONES WHERE \"FILE\"=" + parent.id.ToString();
                FbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Phone _phone = new Phone(parent);
                    if (!dr.IsDBNull(dr.GetOrdinal("ID"))) _phone.id = dr.GetInt32(dr.GetOrdinal("ID"));
                    else _phone.id = 0;
                    if (!dr.IsDBNull(dr.GetOrdinal("PHONE"))) _phone.PhoneNumber = dr.GetString(dr.GetOrdinal("PHONE"));
                    else _phone.PhoneNumber = "";
                    if (!dr.IsDBNull(dr.GetOrdinal("CATEGORY"))) _phone.Category = dr.GetInt32(dr.GetOrdinal("CATEGORY"));
                    else _phone.Category = -1;
                    result.Add(_phone);
                }
                dr.Close();
                cmd.Dispose();
                return result;
            }
            protected DBActive Parent { get; private set; }
            public int id { get; private set; }
            public string PhoneNumber { get; set; }
            public int Category { get; set; }
            public void Delete()
            {
                if (this.id != 0)
                {
                    FbCommand cmd = this.Parent.Connection.CreateCommand();
                    cmd.Transaction = Parent.Transaction;
                    cmd.CommandText = "DELETE FROM PHONES WHERE ID=" + this.id.ToString();
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }
            public void Save()
            {
                if (this.id != 0)
                {
                    FbCommand cmd = this.Parent.Connection.CreateCommand();
                    cmd.Transaction = Parent.Transaction;
                    cmd.CommandText = "UPDATE PHONES SET PHONE=@PHONE, CATEGORY=@CATEGORY WHERE ID=" + this.id.ToString();
                    cmd.Parameters.Add("PHONE", FbDbType.VarChar, 16).Value = this.PhoneNumber;
                    cmd.Parameters.Add("CATEGORY", FbDbType.Integer).Value = this.Category;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }

            public override string ToString()
            {
                return this.PhoneNumber;
            }
        }

        public class Party
        {
            private Party()
            {
                this.id = 0;
                this.Parent = null;
                this.Caption = "";
            }
            public Party(DBActive parent, int id)
            {
                this.Parent = parent;
                FbCommand cmd = parent.Connection.CreateCommand();
                cmd.Transaction = Parent.Transaction;
                cmd.CommandText = "SELECT CAPTION FROM PARTY WHERE ID=" + id.ToString();
                object caption = cmd.ExecuteScalar().ToString();
                cmd.Dispose();
                if (caption == null || caption == DBNull.Value)
                {
                    this.id = 0;
                    this.Caption = "";
                }
                else
                {
                    this.id = id;
                    this.Caption = caption.ToString();
                }
            }

            public Party(DBActive parent, string caption)
            {
                this.Parent = parent;
                this.Caption = caption;
                FbCommand cmd = parent.Connection.CreateCommand();
                cmd.Transaction = Parent.Transaction;
                cmd.CommandText = "SELECT ID FROM PARTY WHERE (CAPTION=@CAPTION)";
                cmd.Parameters.Add("CAPTION", FbDbType.VarChar, 1000).Value = this.Caption;
                try
                {
                    id = int.Parse(cmd.ExecuteScalar().ToString());
                }
                catch (Exception)
                {
                    cmd.CommandText = "INSERT INTO PARTY (CAPTION) VALUES(@CAPTION) RETURNING ID";
                    id = int.Parse(cmd.ExecuteScalar().ToString());
                }
                cmd.CommandText = "select count(*) from LINKPARTY where (\"FILE\"=@FILE) and (PARTY=@PARTY) ";
                cmd.Parameters.Add("FILE", FbDbType.Integer).Value = parent.id;
                cmd.Parameters.Add("PARTY", FbDbType.Integer).Value = id;
                int cnt = int.Parse(cmd.ExecuteScalar().ToString());
                if (cnt == 0)
                {
                    cmd.CommandText = "INSERT INTO LINKPARTY (\"FILE\",PARTY) VALUES(@FILE,@PARTY)";
                    cmd.ExecuteNonQuery();
                }
                cmd.Dispose();
            }

            public static List<Party> LoadAll(DBActive parent)
            {
                List<Party> result = new List<Party>();
                FbCommand cmd = parent.Connection.CreateCommand();
                cmd.Transaction = parent.Transaction;
                cmd.CommandText = "SELECT PARTY FROM LINKPARTY WHERE \"FILE\"=" + parent.id.ToString();
                FbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Party _party = null;
                    if (!dr.IsDBNull(dr.GetOrdinal("PARTY"))) _party = new Party(parent, dr.GetInt32(dr.GetOrdinal("PARTY")));
                    result.Add(_party);
                }
                dr.Close();
                cmd.Dispose();
                return result;
            }

            public static List<Party> LoadAll(FbConnection Connection, FbTransaction Transaction, string caption)
            {
                List<Party> result = new List<Party>();
                //if (caption.Length < 3) return result;
                FbCommand cmd = Connection.CreateCommand();
                cmd.Transaction = Transaction;
                cmd.CommandText = "SELECT * FROM PARTY WHERE (CAPTION containing @CAPTION) ORDER BY CAPTION ";
                cmd.Parameters.Add("CAPTION", FbDbType.VarChar, 1000).Value = caption;
                FbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Party party = new Party();
                    party.id = dr.GetInt32(dr.GetOrdinal("ID"));
                    party.Caption = dr.GetString(dr.GetOrdinal("CAPTION"));
                    result.Add(party);
                }
                dr.Close();
                cmd.Dispose();
                return result;
            }

            protected DBActive Parent { get; private set; }
            public int id { get; set; }
            public string Caption { get; set; }
            public void Save()
            {
                if (this.id != 0)
                {
                    FbCommand cmd = this.Parent.Connection.CreateCommand();
                    cmd.Transaction = Parent.Transaction;
                    cmd.CommandText = "UPDATE PARTY SET CAPTION=@CAPTION WHERE ID=" + this.id.ToString();
                    cmd.Parameters.Add("CAPTION", FbDbType.VarChar, 1000).Value = this.Caption;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }
            public void Delete()
            {
                if (this.id != 0)
                {
                    FbCommand cmd = this.Parent.Connection.CreateCommand();
                    cmd.Transaction = Parent.Transaction;
                    cmd.CommandText = "DELETE FROM PARTY WHERE ID=" + this.id.ToString();
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }
            public override string ToString()
            {
                return this.Caption;
            }
        }

        public class RajonOfficial
        {
            private RajonOfficial()
            {
                this.Parent = null;
                this.id = 0;
                this.Caption = "";
            }

            public RajonOfficial(DBActive parent)
            {
                this.Parent = parent;
                this.id = 0;
                this.Caption = "";
            }

            public RajonOfficial(DBActive parent, int id)
            {
                this.Parent = parent;
                FbCommand cmd = parent.Connection.CreateCommand();
                cmd.Transaction = Parent.Transaction;
                cmd.CommandText = "SELECT CAPTION FROM RAJONS_OFFICIAL WHERE ID=" + id.ToString();
                object caption = cmd.ExecuteScalar().ToString();
                cmd.Dispose();
                if (caption == null || caption == DBNull.Value)
                {
                    this.id = 0;
                    this.Caption = "";
                }
                else
                {
                    this.id = id;
                    this.Caption = caption.ToString();
                }
            }


            public RajonOfficial(DBActive parent, string caption)
                : this(parent)
            {
                this.Caption = caption;
                FbCommand cmd = parent.Connection.CreateCommand();
                cmd.Transaction = Parent.Transaction;
                try
                {
                    cmd.CommandText = "SELECT ID FROM RAJONS_OFFICIAL WHERE (CAPTION=@CAPTION) ";
                    cmd.Parameters.Add("CAPTION", FbDbType.VarChar, 1000).Value = this.Caption;
                    id = int.Parse(cmd.ExecuteScalar().ToString());
                }
                catch (Exception)
                {
                    cmd.CommandText = "INSERT INTO RAJONS_OFFICIAL (CAPTION) VALUES(@CAPTION) RETURNING ID";
                    id = int.Parse(cmd.ExecuteScalar().ToString());
                }
                cmd.Dispose();
            }

            public void Save()
            {
                if (this.id != 0)
                {
                    FbCommand cmd = this.Parent.Connection.CreateCommand();
                    cmd.Transaction = Parent.Transaction;
                    cmd.CommandText = "UPDATE RAJONS_OFFICIAL SET CAPTION=@CAPTION WHERE ID=" + this.id.ToString();
                    cmd.Parameters.Add("CAPTION", FbDbType.VarChar, 1000).Value = this.Caption;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }

            protected DBActive Parent { get; private set; }
            public int id { get; private set; }
            public string Caption { get; private set; }

            public static List<RajonOfficial> LoadAll(FbConnection Connection, FbTransaction Transaction, string caption)
            {
                List<RajonOfficial> result = new List<RajonOfficial>();
                //if (caption.Length < 3) return result;
                FbCommand cmd = Connection.CreateCommand();
                cmd.Transaction = Transaction;
                cmd.CommandText = "SELECT * FROM RAJONS_OFFICIAL WHERE (CAPTION containing @CAPTION) ORDER BY CAPTION ";
                cmd.Parameters.Add("CAPTION", FbDbType.VarChar, 1000).Value = caption;
                FbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    RajonOfficial item = new RajonOfficial();
                    item.id = dr.GetInt32(dr.GetOrdinal("ID"));
                    item.Caption = dr.GetString(dr.GetOrdinal("CAPTION"));
                    result.Add(item);
                }
                dr.Close();
                cmd.Dispose();
                return result;
            }

        }

        public class RajonNonOfficial
        {
            private RajonNonOfficial()
            {
                this.Parent = null;
                this.id = 0;
                this.Caption = "";
            }
            public RajonNonOfficial(DBActive parent, int id)
            {
                this.Parent = parent;
                FbCommand cmd = parent.Connection.CreateCommand();
                cmd.Transaction = Parent.Transaction;
                cmd.CommandText = "SELECT CAPTION FROM RAJONS_NONOFFICIAL WHERE ID=" + id.ToString();
                object caption = cmd.ExecuteScalar().ToString();
                cmd.Dispose();
                if (caption == null || caption == DBNull.Value)
                {
                    this.id = 0;
                    this.Caption = "";
                }
                else
                {
                    this.id = id;
                    this.Caption = caption.ToString();
                }
            }

            public RajonNonOfficial(DBActive parent)
            {
                this.Parent = parent;
                this.id = 0;
                this.Caption = "";
            }

            public RajonNonOfficial(DBActive parent, string caption)
                : this(parent)
            {
                this.Caption = caption;
                FbCommand cmd = parent.Connection.CreateCommand();
                cmd.Transaction = Parent.Transaction;
                try
                {
                    cmd.CommandText = "SELECT ID FROM RAJONS_NONOFFICIAL WHERE (CAPTION=@CAPTION) ";
                    cmd.Parameters.Add("CAPTION", FbDbType.VarChar, 1000).Value = this.Caption;
                    id = int.Parse(cmd.ExecuteScalar().ToString());
                }
                catch (Exception)
                {
                    cmd.CommandText = "INSERT INTO RAJONS_NONOFFICIAL (CAPTION) VALUES(@CAPTION) RETURNING ID";
                    cmd.Parameters.Add("CAPTION", FbDbType.VarChar, 1000).Value = this.Caption;
                    id = int.Parse(cmd.ExecuteScalar().ToString());
                }
                cmd.Dispose();
            }

            public void Save()
            {
                if (this.id != 0)
                {
                    FbCommand cmd = this.Parent.Connection.CreateCommand();
                    cmd.CommandText = "UPDATE RAJONS_NONOFFICIAL SET CAPTION=@CAPTION WHERE ID=" + this.id.ToString();
                    cmd.Transaction = Parent.Transaction;
                    cmd.Parameters.Add("CAPTION", FbDbType.VarChar, 1000).Value = this.Caption;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }

            protected DBActive Parent { get; private set; }
            public int id { get; set; }
            public string Caption { get; set; }

            public static List<RajonNonOfficial> LoadAll(FbConnection Connection, FbTransaction Transaction, string caption)
            {
                List<RajonNonOfficial> result = new List<RajonNonOfficial>();
                //if (caption.Length < 3) return result;
                FbCommand cmd = Connection.CreateCommand();
                cmd.Transaction = Transaction;
                cmd.CommandText = "SELECT * FROM RAJONS_NONOFFICIAL WHERE (CAPTION containing @CAPTION) ORDER BY CAPTION ";
                cmd.Parameters.Add("CAPTION", FbDbType.VarChar, 1000).Value = caption;
                FbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    RajonNonOfficial item = new RajonNonOfficial();
                    item.id = dr.GetInt32(dr.GetOrdinal("ID"));
                    item.Caption = dr.GetString(dr.GetOrdinal("CAPTION"));
                    result.Add(item);
                }
                dr.Close();
                cmd.Dispose();
                return result;
            }

            public override string ToString()
            {
                return this.Caption;
            }
        }

        public class Action
        {
            public DBActive Parent { get; private set; }

            public int id { get; private set; }

            public DateTime? ActionDate { get; set; }

            public string Theme { get; set; }

            public string Note { get; set; }

            public Action(DBActive parent)
            {
                this.id = 0;
                this.ActionDate = null;
                this.Theme = "";
                this.Note = "";
                this.Parent = parent;
            }

            public Action(DBActive parent, DateTime? ActionDate, string Theme, string Note): this(parent)
            {
                this.ActionDate = ActionDate;
                this.Theme = Theme;
                this.Note = Note;
                FbCommand cmd = parent.Connection.CreateCommand();
                cmd.Transaction = Parent.Transaction;
                cmd.CommandText = "INSERT INTO ACTIONS (\"FILE\", ACTIONDATE, THEME, NOTE) VALUES(@FILE, @ACTIONDATE, @THEME, @NOTE) RETURNING ID";
                cmd.Parameters.Add("FILE", FbDbType.Integer).Value = this.Parent.id;
                if (this.ActionDate != null) cmd.Parameters.Add("ACTIONDATE", FbDbType.TimeStamp).Value = this.ActionDate;
                else cmd.Parameters.Add("ACTIONDATE", FbDbType.TimeStamp).IsNullable = true;
                cmd.Parameters.Add("THEME", FbDbType.VarChar, 1000).Value = this.Theme;
                cmd.Parameters.Add("NOTE", FbDbType.VarChar, 10000).Value = this.Note;
                id = int.Parse(cmd.ExecuteScalar().ToString());
                cmd.Dispose();
            }

            public Action(DBActive parent, int id): this(parent)
            {
                this.id = id;
                FbCommand cmd = parent.Connection.CreateCommand();
                cmd.Transaction = Parent.Transaction;
                cmd.CommandText = "SELECT * FROM ACTIONS WHERE ID=" + id.ToString();
                FbDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    if (!dr.IsDBNull(dr.GetOrdinal("ACTIONDATE"))) this.ActionDate = (DateTime)dr.GetDateTime(dr.GetOrdinal("ACTIONDATE"));
                    else this.ActionDate = null;
                    if (!dr.IsDBNull(dr.GetOrdinal("THEME"))) this.Theme = dr.GetString(dr.GetOrdinal("THEME"));
                    else this.Theme = "";
                    if (!dr.IsDBNull(dr.GetOrdinal("NOTE"))) this.Note = dr.GetString(dr.GetOrdinal("NOTE"));
                    else this.Note = "";
                }
                dr.Close();
                cmd.Dispose();
            }

            public static List<Action> LoadAll(DBActive parent)
            {
                List<Action> result = new List<Action>();
                FbCommand cmd = parent.Connection.CreateCommand();
                cmd.Transaction = parent.Transaction;
                cmd.CommandText = "SELECT * FROM ACTIONS WHERE \"FILE\"=" + parent.id.ToString();
                FbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Action action = new Action(parent);
                    action.id = dr.GetInt32(dr.GetOrdinal("ID"));
                    if (!dr.IsDBNull(dr.GetOrdinal("ACTIONDATE"))) action.ActionDate = (DateTime)dr.GetDateTime(dr.GetOrdinal("ACTIONDATE"));
                    else action.ActionDate = null;
                    if (!dr.IsDBNull(dr.GetOrdinal("THEME"))) action.Theme = dr.GetString(dr.GetOrdinal("THEME"));
                    else action.Theme = "";
                    if (!dr.IsDBNull(dr.GetOrdinal("NOTE"))) action.Note = dr.GetString(dr.GetOrdinal("NOTE"));
                    else action.Note = "";
                    result.Add(action);
                }
                dr.Close();
                cmd.Dispose();
                return result;
            }

            public void Delete()
            {
                FbCommand cmd = Parent.Connection.CreateCommand();
                cmd.Transaction = Parent.Transaction;
                cmd.CommandText = "DELETE FROM ACTIONS WHERE ID=" + id.ToString();
                cmd.ExecuteNonQuery();
            }

            public void Save()
            {
                if (this.id != 0)
                {
                    FbCommand cmd = this.Parent.Connection.CreateCommand();
                    cmd.Transaction = Parent.Transaction;
                    cmd.CommandText = "UPDATE ACTIONS SET ACTIONDATE=@ACTIONDATE,THEME=@THEME,NOTE=@NOTE WHERE ID=" + this.id.ToString();
                    if (this.ActionDate != null) cmd.Parameters.Add("ACTIONDATE", FbDbType.TimeStamp).Value = this.ActionDate;
                    else cmd.Parameters.Add("ACTIONDATE", FbDbType.TimeStamp).IsNullable = true;
                    cmd.Parameters.Add("THEME", FbDbType.VarChar, 1000).Value = this.Theme;
                    cmd.Parameters.Add("NOTE", FbDbType.VarChar, 10000).Value = this.Note;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }
        }


    }
}
